// 博客数据分析API - 管理端博客数据统计
const express = require('express');
const db = require('../config/database');

const router = express.Router();

// 获取博客总体统计数据
router.get('/blog-analytics/overview', async (req, res) => {
    try {
        // 获取文章总数
        const [articleCount] = await db.query(`
            SELECT COUNT(*) as total_articles 
            FROM articles 
            WHERE status = 'published'
        `);
        
        // 获取总浏览量
        const [totalViews] = await db.query(`
            SELECT SUM(views) as total_views 
            FROM articles 
            WHERE status = 'published'
        `);
        
        // 获取总点赞数
        const [totalLikes] = await db.query(`
            SELECT SUM(likes) as total_likes 
            FROM articles 
            WHERE status = 'published'
        `);
        
        // 获取总评论数
        const [totalComments] = await db.query(`
            SELECT SUM(comments) as total_comments 
            FROM articles 
            WHERE status = 'published'
        `);
        
        // 获取最近7天数据
        const [recentStats] = await db.query(`
            SELECT 
                COUNT(*) as recent_articles,
                SUM(views) as recent_views,
                SUM(likes) as recent_likes,
                SUM(comments) as recent_comments
            FROM articles 
            WHERE status = 'published' 
            AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        `);
        
        res.json({
            success: true,
            data: {
                total_articles: articleCount[0].total_articles || 0,
                total_views: totalViews[0].total_views || 0,
                total_likes: totalLikes[0].total_likes || 0,
                total_comments: totalComments[0].total_comments || 0,
                recent_stats: recentStats[0] || {}
            }
        });
        
    } catch (error) {
        console.error('获取博客统计数据失败:', error);
        res.status(500).json({
            success: false,
            message: '获取统计数据失败'
        });
    }
});

// 获取分类统计数据
router.get('/blog-analytics/categories', async (req, res) => {
    try {
        const [categoryStats] = await db.query(`
            SELECT 
                c.name as category_name,
                c.id as category_id,
                COUNT(a.id) as article_count,
                SUM(a.views) as total_views,
                SUM(a.likes) as total_likes,
                SUM(a.comments) as total_comments
            FROM blog_categories c
            LEFT JOIN articles a ON c.id = a.category_id AND a.status = 'published'
            GROUP BY c.id, c.name
            ORDER BY article_count DESC
        `);
        
        res.json({
            success: true,
            data: categoryStats
        });
        
    } catch (error) {
        console.error('获取分类统计数据失败:', error);
        res.status(500).json({
            success: false,
            message: '获取分类统计数据失败'
        });
    }
});

// 获取热门文章
router.get('/blog-analytics/popular-articles', async (req, res) => {
    try {
        const { limit = 10 } = req.query;
        
        const [popularArticles] = await db.query(`
            SELECT 
                a.id,
                a.title,
                a.views,
                a.likes,
                a.comments,
                a.created_at,
                c.name as category_name
            FROM articles a
            LEFT JOIN blog_categories c ON a.category_id = c.id
            WHERE a.status = 'published'
            ORDER BY a.views DESC
            LIMIT ?
        `, [parseInt(limit)]);
        
        res.json({
            success: true,
            data: popularArticles
        });
        
    } catch (error) {
        console.error('获取热门文章失败:', error);
        res.status(500).json({
            success: false,
            message: '获取热门文章失败'
        });
    }
});

// 获取用户访问趋势
router.get('/blog-analytics/visitor-trends', async (req, res) => {
    try {
        const { days = 30 } = req.query;
        
        const [visitorTrends] = await db.query(`
            SELECT 
                DATE(created_at) as date,
                COUNT(*) as page_views,
                COUNT(DISTINCT visitor_id) as unique_visitors
            FROM visitor_events 
            WHERE event_type = 'page_view' 
            AND page_url LIKE '%/blog%'
            AND created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
            GROUP BY DATE(created_at)
            ORDER BY date ASC
        `, [parseInt(days)]);
        
        res.json({
            success: true,
            data: visitorTrends
        });
        
    } catch (error) {
        console.error('获取访问趋势失败:', error);
        res.status(500).json({
            success: false,
            message: '获取访问趋势失败'
        });
    }
});

// 获取文章详情统计数据
router.get('/blog-analytics/article/:id', async (req, res) => {
    try {
        const articleId = parseInt(req.params.id);
        
        const [articleStats] = await db.query(`
            SELECT 
                a.*,
                c.name as category_name,
                col.name as column_name
            FROM articles a
            LEFT JOIN blog_categories c ON a.category_id = c.id
            LEFT JOIN blog_columns col ON a.column_id = col.id
            WHERE a.id = ?
        `, [articleId]);
        
        if (articleStats.length === 0) {
            return res.status(404).json({
                success: false,
                message: '文章不存在'
            });
        }
        
        // 获取文章阅读趋势
        const [readTrends] = await db.query(`
            SELECT 
                DATE(created_at) as date,
                COUNT(*) as views
            FROM visitor_events 
            WHERE event_type = 'page_view' 
            AND page_url LIKE CONCAT('%', ?, '%')
            AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY DATE(created_at)
            ORDER BY date ASC
        `, [articleId]);
        
        res.json({
            success: true,
            data: {
                article: articleStats[0],
                read_trends: readTrends
            }
        });
        
    } catch (error) {
        console.error('获取文章统计数据失败:', error);
        res.status(500).json({
            success: false,
            message: '获取文章统计数据失败'
        });
    }
});

module.exports = router;